package lab;

import java.sql.*;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class Database {

    private String host = "jdbc:mysql://localhost:3306/mydb";
    private String uName = "root";
    private String uPass = "@jagali85@";
    private String SQL;
    JTable table = new JTable();
    Vector tableVector;
    DefaultTableModel tableModel;
    private Connection con;
    private Statement stmt;
    private ResultSet rs;
    private PreparedStatement pr;
    CenterPanelRear centerPanelRear = new CenterPanelRear(null);

    public Database(JTable table, DefaultTableModel tableModel, CenterPanelRear centerPanelRear) {
        this.tableModel = tableModel;
        this.centerPanelRear = centerPanelRear;
        this.table = table;
        tableVector = new Vector();
        connect();
    }

    public void connect() {
        try {
            con = DriverManager.getConnection(host, uName, uPass);
            System.out.println("Connected");
        } catch (SQLException err) {
            System.out.println(err.getMessage());
        }
    }

    public void searchAnything(String anyThing) {
        tableVector.removeAllElements();
        tableModel.setRowCount(0);
        SQL = "SELECT * FROM books WHERE Title LIKE '%" + anyThing + "%' OR Author LIKE '%" + anyThing + "%' OR Genre LIKE '%" + anyThing + "%'  OR Isbn LIKE '%" + anyThing + "%' OR Year LIKE '%" + anyThing + "%' OR Price LIKE '%" + anyThing + "%'";
        searchDatabase(SQL);
    }

    public void searchGenreOnly(String genre) {
        tableModel.setRowCount(0);
        tableVector.removeAllElements();
        SQL = "SELECT * FROM books WHERE Genre LIKE '%" + genre + "%'";
        searchDatabase(SQL);
    }

    public void updateStock(String ISBN) throws SQLException {
        int stock = 0;
        for (int index = 0; index < centerPanelRear.arrayListISBN.size() - 1; index++) {
            ISBN = centerPanelRear.arrayListISBN.get(index);
            stock = getStock(ISBN);
            stock = stock - 1;
            SQL = "UPDATE books SET Stock = '" + stock + "' WHERE Isbn ='" + ISBN + "';";
            System.out.println(SQL);
            pr = con.prepareStatement(SQL);
            pr.execute();
        }
        centerPanelRear.arrayListISBN.clear();
        centerPanelRear.arrayList.clear();
        centerPanelRear.booksSum = 0;
        centerPanelRear.eastPanel.textAreaVaruKorg.setText("");
    }

    public String getDescription(String desc) {
        SQL = "SELECT * FROM Books WHERE Title LIKE '%" + desc + "%'";
        String s = "";
        tableVector = new Vector();
        tableVector.setSize(1);

        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                tableVector.add(rs.getString("Description"));
            }
            s = tableVector.get(1).toString();
            rs.close();
        } catch (SQLException ex) {
            System.out.println("Cannt get description");
        }
        return s;
    }

    public void searchDatabase(String SQL) {

        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            tableModel = (DefaultTableModel) table.getModel();
            while (rs.next()) {
                tableVector = new Vector();
                tableVector.add(rs.getString("Title"));
                tableVector.add(rs.getString("Author"));
                tableVector.add(rs.getString("Genre"));
                tableVector.add(rs.getString("Isbn"));
                tableVector.add(rs.getString("Year"));
                tableVector.add(rs.getString("Stock"));
                tableVector.add(rs.getString("Price"));
                addSearchResult();
            }
            rs.close();
        } catch (SQLException err) {
            System.out.print("Cannt search");
            System.out.println(err.getMessage());
        }
    }

    public void addSearchResult() {
        tableModel.addRow(tableVector);
    }

    public int getStock(String ISBN) {
        SQL = "SELECT Stock FROM books WHERE Isbn ='" + ISBN + "'";
        int stock = 0;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                stock = rs.getInt("Stock");
            }
        } catch (SQLException ex) {
        }
        return stock;
    }
}